package com.woniu.mapper;


import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.woniu.been.entity.Video;

@Mapper
public interface VideoMapper {

    @Insert("INSERT INTO video (course_id, chapter_id, file_name, title, link, create_name, create_time) " +
            "VALUES (#{courseId}, #{chapterId}, #{fileName}, #{title}, #{link}, #{createName}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "videoId")
    int insert(Video video);
    
    @Select("SELECT * FROM video WHERE course_id = #{courseId}")
    List<Video> findByCourseId(Integer courseId);
    
    @Update("UPDATE video SET title = #{title}, amend_time = NOW() " +
            "WHERE video_id = #{videoId}")
    int updateTitle(@Param("videoId") Integer videoId, 
                   @Param("title") String title);
                   
    @Delete("DELETE FROM video WHERE file_name = #{fileName}")
    int deleteByFileName(String fileName);
    
    @Select("SELECT * FROM video WHERE file_name = #{fileName}")
    Video findByFileName(String fileName);

    @Select("<script>" +
            "SELECT v.*, cc.category_name, c.course_name as course_name " +
            "FROM video v " +
            "LEFT JOIN courses c ON v.course_id = c.course_id " +
            "LEFT JOIN course_categories cc ON c.category_id = cc.category_id " +
            "WHERE v.course_id = #{courseId} " +
            "<if test='categoryName != null and categoryName != \"\"'>" +
            "  AND cc.category_name = #{categoryName} " +
            "</if>" +
            "</script>")
    List<Video> findByCourseIdAndFilters(@Param("courseId") Integer courseId,
                                         @Param("categoryName") String categoryName);
    
    /**
     * 根据章节ID获取视频列表
     * @param chapterId 章节ID
     * @return 视频列表
     */
    @Select("SELECT v.* FROM video v WHERE v.chapter_id = #{chapterId}")
    List<Video> findByChapterId(Integer chapterId);
    
    /**
     * 根据课程小类ID获取视频列表
     * @param subCategoryId 课程小类ID
     * @return 视频列表
     */
    @Select("SELECT v.* FROM video v " +
            "JOIN courses c ON v.course_id = c.course_id " +
            "JOIN course_chapters_relations ccr ON c.course_id = ccr.course_id " +
            "JOIN course_category_relations ccatr ON ccr.chapter_id = ccatr.chapter_id " +
            "WHERE ccatr.category_id = #{subCategoryId}")
    List<Video> findBySubCategoryId(Integer subCategoryId);
    
    /**
     * 根据课程大类ID获取视频列表
     * @param mainCategoryId 课程大类ID
     * @return 视频列表
     */
    @Select("SELECT v.* FROM video v " +
            "JOIN courses c ON v.course_id = c.course_id " +
            "JOIN course_chapters_relations ccr ON c.course_id = ccr.course_id " +
            "JOIN course_category_relations ccatr ON ccr.chapter_id = ccatr.chapter_id " +
            "JOIN course_categories cc ON ccatr.category_id = cc.category_id " +
            "WHERE cc.parent_category_id = #{mainCategoryId}")
    List<Video> findByMainCategoryId(Integer mainCategoryId);
} 